EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'RDW'
GO
USE [master]
GO
ALTER DATABASE [RDW] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [RDW]
GO

USE Master
GO
CREATE DATABASE [RDW]
GO
USE [RDW]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VisitRequest]') AND type in (N'U'))
    DROP TABLE [dbo].[VisitRequest]
GO
CREATE TABLE [dbo].[VisitRequest] (
[VisitRequestId] int identity(1,1)  NOT NULL  
, [RequestId] int  NOT NULL  
, [CompanyName] nvarchar(50)  NULL  
, [PersonNum] int  NULL  
, [VisitType1] int  NULL  
, [VisitType2] int  NULL  
, [MainPersonName] nvarchar(20)  NULL  
, [MainPersonTitle] nvarchar(20)  NULL  
, [Person2Name] nvarchar(20)  NULL  
, [Person2Title] nvarchar(20)  NULL  
, [Person3Name] nvarchar(20)  NULL  
, [Person3Title] nvarchar(20)  NULL  
, [IsFirstTime] bit  NOT NULL  
, [LastDate] date  NULL  
, [Purpose] nvarchar(50)  NULL  
, [KeyContent] nvarchar(100)  NULL  
, [OtherRequirements] nvarchar(100)  NULL  
, [Accompany] int  NULL  
, [AccompanyManager] nvarchar(20)  NULL  
, [AccompanyManagerTitle] nvarchar(20)  NULL  
)
GO

ALTER TABLE [dbo].[VisitRequest] ADD CONSTRAINT [VisitRequest_PK] PRIMARY KEY CLUSTERED (
[VisitRequestId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RentRequest]') AND type in (N'U'))
    DROP TABLE [dbo].[RentRequest]
GO
CREATE TABLE [dbo].[RentRequest] (
[Id] int identity(1,1)  NOT NULL  
, [RequestId] int  NULL  
, [Purpose] nvarchar(50)  NULL  
, [RentPerson] nvarchar(20)  NULL  
, [PersonNum] int  NULL  
, [OtherRequirements] nvarchar(100)  NULL  
)
GO

ALTER TABLE [dbo].[RentRequest] ADD CONSTRAINT [RentRequest_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Answer]') AND type in (N'U'))
    DROP TABLE [dbo].[Answer]
GO
CREATE TABLE [dbo].[Answer] (
[Id] int identity(1,1)  NOT NULL  
, [QuestionId] int  NOT NULL  
, [Answer_CN] nvarchar(100)  NULL  
, [Answer_EN] nvarchar(100)  NULL  
, [ControlType] nvarchar(20)  NULL  
)
GO

ALTER TABLE [dbo].[Answer] ADD CONSTRAINT [Answer_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Role]') AND type in (N'U'))
    DROP TABLE [dbo].[Role]
GO
CREATE TABLE [dbo].[Role] (
[RoleId] int identity(1,1)  NOT NULL  
, [Name] nvarchar(50)  NOT NULL  
, [Desc] nvarchar(100)  NULL  
)
GO

ALTER TABLE [dbo].[Role] ADD CONSTRAINT [Role_PK] PRIMARY KEY CLUSTERED (
[RoleId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRequest]') AND type in (N'U'))
    DROP TABLE [dbo].[TestRequest]
GO
CREATE TABLE [dbo].[TestRequest] (
[TestRequestId] int identity(1,1)  NOT NULL  
, [RequestId] int  NULL  
, [Content] nvarchar(200)  NULL  
, [PersonNum] int  NULL  
, [OtherRequirements] nvarchar(100)  NULL  
)
GO

ALTER TABLE [dbo].[TestRequest] ADD CONSTRAINT [TestRequest_PK] PRIMARY KEY CLUSTERED (
[TestRequestId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRole]') AND type in (N'U'))
    DROP TABLE [dbo].[UserRole]
GO
CREATE TABLE [dbo].[UserRole] (
[UserRoleId] int identity(1,1)  NOT NULL  
, [UserId] int  NOT NULL  
, [RoleId] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[UserRole] ADD CONSTRAINT [UserRole_PK] PRIMARY KEY CLUSTERED (
[UserRoleId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PerfectPicture]') AND type in (N'U'))
    DROP TABLE [dbo].[PerfectPicture]
GO
CREATE TABLE [dbo].[PerfectPicture] (
[Id] int identity(1,1)  NOT NULL  
, [Path] nvarchar(100)  NOT NULL  
, [Desc_CN] nvarchar(100)  NULL  
, [Desc_EN] nvarchar(100)  NULL  
, [CreatTime] datetime  NULL  
, [CreateBy] int  NULL  
)
GO

ALTER TABLE [dbo].[PerfectPicture] ADD CONSTRAINT [PerfectPicture_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FeedbackAnswer]') AND type in (N'U'))
    DROP TABLE [dbo].[FeedbackAnswer]
GO
CREATE TABLE [dbo].[FeedbackAnswer] (
[Id] int identity(1,1)  NOT NULL  
, [QuestionId] int  NULL  
, [AnswerId] int  NULL  
, [FeedbackId] int  NULL  
)
GO

ALTER TABLE [dbo].[FeedbackAnswer] ADD CONSTRAINT [FeedbackAnswer_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SysLog]') AND type in (N'U'))
    DROP TABLE [dbo].[SysLog]
GO
CREATE TABLE [dbo].[SysLog] (
[Id] int identity(1,1)  NOT NULL  
, [Desc] nvarchar(200)  NOT NULL  
, [CreateTime] datetime  NOT NULL  
, [CreateBy] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[SysLog] ADD CONSTRAINT [SysLog_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VisitType]') AND type in (N'U'))
    DROP TABLE [dbo].[VisitType]
GO
CREATE TABLE [dbo].[VisitType] (
[Id] int identity(1,1)  NOT NULL  
, [Name_CN] nvarchar(50)  NOT NULL  
, [Name_EN] nvarchar(50)  NOT NULL  
, [ParentId] int  NOT NULL  
, [Level] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[VisitType] ADD CONSTRAINT [VisitType_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
    DROP TABLE [dbo].[Location]
GO
CREATE TABLE [dbo].[Location] (
[LocationId] int identity(1,1)  NOT NULL  
, [Name_CN] nvarchar(20)  NOT NULL  
, [Name_EN] nvarchar(20)  NOT NULL  
)
GO

ALTER TABLE [dbo].[Location] ADD CONSTRAINT [Location_PK] PRIMARY KEY CLUSTERED (
[LocationId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Announcement]') AND type in (N'U'))
    DROP TABLE [dbo].[Announcement]
GO
CREATE TABLE [dbo].[Announcement] (
[Id] int identity(1,1)  NOT NULL  
, [Language] nvarchar(4)  NOT NULL  
, [Subject] nvarchar(100)  NOT NULL  
, [Body] nvarchar(MAX)  NOT NULL  
, [CreateTime] datetime  NOT NULL  
, [CreateBy] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[Announcement] ADD CONSTRAINT [Announcement_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
    DROP TABLE [dbo].[Department]
GO
CREATE TABLE [dbo].[Department] (
[DepId] int identity(1,1)  NOT NULL  
, [Name_CN] nvarchar(20)  NULL  
, [Name_EN] nvarchar(20)  NULL  
, [Platform] nvarchar(20)  NULL  
, [Code] nvarchar(20)  NULL  
, [CreditScore] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[Department] ADD CONSTRAINT [Department_PK] PRIMARY KEY CLUSTERED (
[DepId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VisitProduct]') AND type in (N'U'))
    DROP TABLE [dbo].[VisitProduct]
GO
CREATE TABLE [dbo].[VisitProduct] (
[Id] int identity(1,1)  NOT NULL  
, [VisitRequestId] int  NULL  
, [ProductId] int  NULL  
)
GO

ALTER TABLE [dbo].[VisitProduct] ADD CONSTRAINT [VisitProduct_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lab]') AND type in (N'U'))
    DROP TABLE [dbo].[Lab]
GO
CREATE TABLE [dbo].[Lab] (
[LabId] int identity(1,1)  NOT NULL  
, [LocationId] int  NOT NULL  
, [Name] nvarchar(50)  NOT NULL  
, [Desc] nvarchar(100)  NULL  
)
GO

ALTER TABLE [dbo].[Lab] ADD CONSTRAINT [Lab_PK] PRIMARY KEY CLUSTERED (
[LabId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SysSetting]') AND type in (N'U'))
    DROP TABLE [dbo].[SysSetting]
GO
CREATE TABLE [dbo].[SysSetting] (
[Id] int identity(1,1)  NOT NULL  
, [Key] nvarchar(50)  NOT NULL  
, [Value] nvarchar(20)  NOT NULL  
, [Comments] nvarchar(100)  NULL  
)
GO

ALTER TABLE [dbo].[SysSetting] ADD CONSTRAINT [SysSetting_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
    DROP TABLE [dbo].[User]
GO
CREATE TABLE [dbo].[User] (
[UserId] int identity(1,1)  NOT NULL  
, [Account] nvarchar(20)  NOT NULL  
, [DepId] int  NULL  
, [EmployeeNo] nvarchar(20)  NULL  
, [NameEN] nvarchar(100)  NULL  
, [NameCN] nvarchar(100)  NULL  
, [NameChinese] nvarchar(100)  NULL  
, [Title] nvarchar(100)  NULL  
, [OfficePhone] nvarchar(60)  NULL  
, [Mobile] nvarchar(60)  NULL  
, [Email] nvarchar(60)  NULL  
, [ReportTo] nvarchar(20)  NULL  
, [BG] nvarchar(20)  NULL  
, [BD] nvarchar(20)  NULL  
, [IsActive] bit  NULL  
, [SyncTime] datetime  NOT NULL  
)
GO

ALTER TABLE [dbo].[User] ADD CONSTRAINT [User_PK] PRIMARY KEY CLUSTERED (
[UserId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
    DROP TABLE [dbo].[Product]
GO
CREATE TABLE [dbo].[Product] (
[ProductId] int identity(1,1)  NOT NULL  
, [LabId] int  NOT NULL  
, [Name_CN] nvarchar(200)  NOT NULL  
, [DemoType_CN] nvarchar(200)  NULL  
, [DemoContent_CN] nvarchar(2000)  NULL  
, [DemoStep_CN] nvarchar(2000)  NULL  
, [KeyComments_CN] nvarchar(2000)  NULL  
, [Equipment_CN] nvarchar(500)  NULL  
, [Name_EN] nvarchar(200)  NOT NULL  
, [DemoType_EN] nvarchar(200)  NULL  
, [DemoContent_EN] nvarchar(2000)  NULL  
, [DemoStep_EN] nvarchar(2000)  NULL  
, [KeyComment_EN] nvarchar(2000)  NULL  
, [Equipment_EN] nvarchar(500)  NULL  
, [ChineseIntroduce] bit  NOT NULL  
, [EnglishIntroduce] bit  NOT NULL  
, [Status] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [Product_PK] PRIMARY KEY CLUSTERED (
[ProductId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LabManager]') AND type in (N'U'))
    DROP TABLE [dbo].[LabManager]
GO
CREATE TABLE [dbo].[LabManager] (
[Id] int identity(1,1)  NOT NULL  
, [LabId] int  NULL  
, [RoleId] int  NULL  
)
GO

ALTER TABLE [dbo].[LabManager] ADD CONSTRAINT [LabManager_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RequestLog]') AND type in (N'U'))
    DROP TABLE [dbo].[RequestLog]
GO
CREATE TABLE [dbo].[RequestLog] (
[LogId] int identity(1,1)  NOT NULL  
, [RequestId] int  NOT NULL  
, [StatusFrom] int  NOT NULL  
, [StatusTo] int  NOT NULL  
, [Comments] nvarchar(100)  NULL  
, [CreateTime] datetime  NOT NULL  
)
GO

ALTER TABLE [dbo].[RequestLog] ADD CONSTRAINT [RequestLog_PK] PRIMARY KEY CLUSTERED (
[LogId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Priviledge]') AND type in (N'U'))
    DROP TABLE [dbo].[Priviledge]
GO
CREATE TABLE [dbo].[Priviledge] (
[Id] int identity(1,1)  NOT NULL  
, [RoleId] int  NULL  
, [UserId] int  NULL  
, [PagePath] nvarchar(100)  NOT NULL  
, [Enable] bit  NOT NULL  
)
GO

ALTER TABLE [dbo].[Priviledge] ADD CONSTRAINT [Priviledge_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SiteManager]') AND type in (N'U'))
    DROP TABLE [dbo].[SiteManager]
GO
CREATE TABLE [dbo].[SiteManager] (
[Id] int identity(1,1)  NOT NULL  
, [LocationId] int  NULL  
, [RoleId] int  NULL  
)
GO

ALTER TABLE [dbo].[SiteManager] ADD CONSTRAINT [SiteManager_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MailTemplate]') AND type in (N'U'))
    DROP TABLE [dbo].[MailTemplate]
GO
CREATE TABLE [dbo].[MailTemplate] (
[Id] int identity(1,1)  NOT NULL  
, [Name] nvarchar(100)  NULL  
, [Subject] nvarchar(100)  NULL  
, [Template] nvarchar(2000)  NULL  
)
GO

ALTER TABLE [dbo].[MailTemplate] ADD CONSTRAINT [MailTemplate_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPic]') AND type in (N'U'))
    DROP TABLE [dbo].[ProductPic]
GO
CREATE TABLE [dbo].[ProductPic] (
[ProductPicId] int identity(1,1)  NOT NULL  
, [ProductId] int  NOT NULL  
, [Path] nvarchar(100)  NOT NULL  
)
GO

ALTER TABLE [dbo].[ProductPic] ADD CONSTRAINT [ProductPic_PK] PRIMARY KEY CLUSTERED (
[ProductPicId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Request]') AND type in (N'U'))
    DROP TABLE [dbo].[Request]
GO
CREATE TABLE [dbo].[Request] (
[RequestId] int identity(1,1)  NOT NULL  
, [LabId] int  NOT NULL  
, [RequestDate] date  NOT NULL  
, [StartTime] time(7)  NOT NULL  
, [EndTime] time(7)  NOT NULL  
, [RequestType] int  NULL  
, [RequestUserId] int  NOT NULL  
, [Status] int  NOT NULL  
, [CreateTime] datetime  NOT NULL  
, [Comments] nvarchar(1000)  NULL  
)
GO

ALTER TABLE [dbo].[Request] ADD CONSTRAINT [Request_PK] PRIMARY KEY CLUSTERED (
[RequestId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reason]') AND type in (N'U'))
    DROP TABLE [dbo].[Reason]
GO
CREATE TABLE [dbo].[Reason] (
[ReasonId] int identity(1,1)  NOT NULL  
, [Desc_CN] nvarchar(50)  NOT NULL  
, [Desc_EN] nvarchar(50)  NOT NULL  
)
GO

ALTER TABLE [dbo].[Reason] ADD CONSTRAINT [Reason_PK] PRIMARY KEY CLUSTERED (
[ReasonId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LabCloseSchedule]') AND type in (N'U'))
    DROP TABLE [dbo].[LabCloseSchedule]
GO
CREATE TABLE [dbo].[LabCloseSchedule] (
[ScheduleId] int identity(1,1)  NOT NULL  
, [StartDate] date  NOT NULL  
, [EndDate] date  NOT NULL  
, [ReasonId] int  NOT NULL  
, [OtherReason] nvarchar(100)  NULL  
, [Solution_CN] nvarchar(200)  NULL  
, [Solution_EN] nvarchar(200)  NULL  
, [LabId] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[LabCloseSchedule] ADD CONSTRAINT [LabCloseSchedule_PK] PRIMARY KEY CLUSTERED (
[ScheduleId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Feedback]') AND type in (N'U'))
    DROP TABLE [dbo].[Feedback]
GO
CREATE TABLE [dbo].[Feedback] (
[FeedbackId] int identity(1,1)  NOT NULL  
, [RequestId] int  NOT NULL  
, [Status] int  NOT NULL  
, [IsPublished] bit  NOT NULL  
)
GO

ALTER TABLE [dbo].[Feedback] ADD CONSTRAINT [Feedback_PK] PRIMARY KEY CLUSTERED (
[FeedbackId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestPerson]') AND type in (N'U'))
    DROP TABLE [dbo].[TestPerson]
GO
CREATE TABLE [dbo].[TestPerson] (
[Id] int identity(1,1)  NOT NULL  
, [TestRequestId] int  NOT NULL  
, [Name] nvarchar(20)  NULL  
, [Dep] nvarchar(20)  NULL  
, [Mobile] nvarchar(20)  NULL  
, [Telephone] nvarchar(20)  NULL  
, [Email] nvarchar(50)  NULL  
)
GO

ALTER TABLE [dbo].[TestPerson] ADD CONSTRAINT [TestPerson_PK] PRIMARY KEY CLUSTERED (
[Id]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Question]') AND type in (N'U'))
    DROP TABLE [dbo].[Question]
GO
CREATE TABLE [dbo].[Question] (
[QuestionId] int identity(1,1)  NOT NULL  
, [Question_CN] nvarchar(200)  NULL  
, [Question_EN] nvarchar(200)  NULL  
, [Status] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[Question] ADD CONSTRAINT [Question_PK] PRIMARY KEY CLUSTERED (
[QuestionId]
)
GO
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LocationDocument]') AND type in (N'U'))
    DROP TABLE [dbo].[LocationDocument]
GO
CREATE TABLE [dbo].[LocationDocument] (
[LocationDocumentId] int identity(1,1)  NOT NULL  
, [LocationId] int  NOT NULL 
, [Name_CN] nvarchar(200)  NULL  
, [Name_EN] nvarchar(200)  NULL  
, [Path] nvarchar(200)  NOT NULL 
, [CreateBy] int  NOT NULL  
, [LastModifyTime] datetime  NOT NULL  
)
GO

ALTER TABLE [dbo].[LocationDocument] ADD CONSTRAINT [LocationDocument_PK] PRIMARY KEY CLUSTERED (
[LocationDocumentId]
)
GO
GO

